﻿<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type='text/xsl' href="scriptsql.xsl"?>
<root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database includeDrop="true">TestDB1</database>
<procedures>
		
		<procedure owner="dbo" name="TstMessage_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Gets all records from the TstMessage table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[MessageID],
					[Message],
					[CreatedDateTime]
				FROM
					[dbo].[TstMessage]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="TstMessage_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Gets records from the TstMessage table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[MessageID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [MessageID]'
				SET @SQL = @SQL + ', [Message]'
				SET @SQL = @SQL + ', [CreatedDateTime]'
				SET @SQL = @SQL + ' FROM [dbo].[TstMessage]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [MessageID],'
				SET @SQL = @SQL + ' [Message],'
				SET @SQL = @SQL + ' [CreatedDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[TstMessage]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="TstMessage_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Inserts a record into the TstMessage table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@MessageID" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Message" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@CreatedDateTime" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[TstMessage]
					(
					[Message]
					,[CreatedDateTime]
					)
				VALUES
					(
					@Message
					,@CreatedDateTime
					)
				
				-- Get the identity value
				SET @MessageID = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="TstMessage_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Updates a record in the TstMessage table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@MessageID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Message" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@CreatedDateTime" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[TstMessage]
				SET
					[Message] = @Message
					,[CreatedDateTime] = @CreatedDateTime
				WHERE
[MessageID] = @MessageID 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="TstMessage_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Deletes a record in the TstMessage table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@MessageID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[TstMessage] WITH (ROWLOCK) 
				WHERE
					[MessageID] = @MessageID
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="TstMessage_GetByMessageID" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Select records from the TstMessage table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@MessageID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[MessageID],
					[Message],
					[CreatedDateTime]
				FROM
					[dbo].[TstMessage]
				WHERE
					[MessageID] = @MessageID
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="TstMessage_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Finds records in the TstMessage table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@MessageID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Message" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
				<parameter name="@CreatedDateTime" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [MessageID]
	, [Message]
	, [CreatedDateTime]
    FROM
	[dbo].[TstMessage]
    WHERE 
	 ([MessageID] = @MessageID OR @MessageID IS NULL)
	AND ([Message] = @Message OR @Message IS NULL)
	AND ([CreatedDateTime] = @CreatedDateTime OR @CreatedDateTime IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [MessageID]
	, [Message]
	, [CreatedDateTime]
    FROM
	[dbo].[TstMessage]
    WHERE 
	 ([MessageID] = @MessageID AND @MessageID is not null)
	OR ([Message] = @Message AND @Message is not null)
	OR ([CreatedDateTime] = @CreatedDateTime AND @CreatedDateTime is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="TstMessage_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Gets records from the TstMessage table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [MessageID]
					, [Message]
					, [CreatedDateTime]
					FROM [dbo].[TstMessage] {0}
				)
				SELECT
				       [MessageID],
				       [Message],
				       [CreatedDateTime]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[TstMessage] {0};
				
				END
			]]></body>
		</procedure>
		
</procedures>
</root>
